import xlrd
import xlwt


class Book:
    def __init__(self, book_name='book.xls'):
        self.book = xlrd.open_workbook(book_name)
        self.note = xlwt.Workbook(encoding='utf-8')
        self.sheet = self.book.sheet_by_index(0)

    def read_col(self, *args):
        """
        竖读
        :return:
        """
        ncols = self.sheet.ncols
        knowlage = list()
        if len(args) == 0:
            for i in range(0, ncols):
                col = self.sheet.col_values(i)
                knowlage.append(col)
            return knowlage
        else:
            for col_index in args:
                col = self.sheet.col_values(col_index)
                knowlage.append(col)
            return knowlage

    def read_row(self, *args):
        """
        横读
        :return:
        """
        nrows = self.sheet.nrows
        knowlage = list()
        if len(args) == 0:
            for i in range(0, nrows):
                row = self.sheet.row_values(i)
                knowlage.append(row)
            return knowlage
        else:
            for row_index in args:
                row = self.sheet.row_values(row_index)
                knowlage.append(row)
            return knowlage

    def read(self):
        """
        自定义读法
        :return:
        """
        pass

    def page(self, page):
        """
        取第几张表
        :param page:
        :return:
        """
        self.sheet = self.book.sheet_by_index(page)

    def write(self):
        """
        写入数据
        :return:
        """
        pass

    # ---------- 工具 ------------
    def mix_col(self, *args, **kwargs):
        """
        组合指定列
        :param args:组合那几列
        :param kwargs: label='' 连接符
        :return:
        """
        nrows = self.sheet.nrows
        knowlage = list()
        info = ''
        label = kwargs.get('label')
        if label is None:
            label = ''
        for i in range(0, nrows):
            con = 0
            for col_index in args:
                if con != 0:
                    info += label
                con += 1
                value = self.num_to_str(self.sheet.cell_value(i, col_index))
                info += value
            knowlage.append(info)
            info = ''
        return knowlage

    # ---------- 格式转换 ---------------
    def num_to_str(self, num):
        """

        :param num:
        :return:
        """
        if type(num) != str:
            return str(int(num))
        else:
            return num


class Note:
    def __init__(self, name='note.xls'):
        self.note = xlwt.Workbook(encoding='utf-8')
        self.sheet = self.note.add_sheet('note')
        self.name = name

    def write_col(self, num, content):
        """
        按列写入
        :param num:
        :param content:
        :return:
        """
        for i in range(0, len(content)):
            self.sheet.write(i, num, label=content[i])

    def write_row(self, row, content, style=''):
        """
        按行写入
        :param row:
        :param content:
        :return:
        """
        if style == '':
            for i in range(0, len(content)):
                self.write_ceil(row, i, label=content[i])
        else:
            for i in range(0, len(content)):
                self.write_ceil(row, i, label=content[i], style=style)

    def write_ceil(self, r, c, label, style=''):
        """
        写入某一单元格
        :param r:
        :param c:
        :return:
        """
        if style == '':
            self.sheet.write(r, c, label)
        else:
            self.sheet.write(r, c, label, style=style)

    def merge_col(self, row, start_col, end_col, style=''):
        """
        合并某一行的列
        :param row:
        :param start_col:
        :param end_col:
        :return:
        """
        if style == '':
            self.sheet.merge(row, row, start_col, end_col)
        else:
            self.sheet.merge(row, row, start_col, end_col, style)

    def make_style(self, *args, **kwargs):
        center = kwargs.get('center')
        color = kwargs.get('color')
        border = kwargs.get('border')
        style = xlwt.XFStyle()
        # ----------- 背景色 --------------
        if color is not None:
            pattern = xlwt.Pattern()
            pattern.pattern = xlwt.Pattern.SOLID_PATTERN
            pattern.pattern_fore_colour = xlwt.Style.colour_map[color]  # 设置单元格背景色为黄色
            style.pattern = pattern
        # ------------ 居中 ---------------
        if center is not None:
            al = xlwt.Alignment()
            al.horz = 0x02  # 设置水平居中
            al.vert = 0x01  # 设置垂直居中
            style.alignment = al
        # ----------- 边框 ---------------
        if border is not None:
            borders = xlwt.Borders()
            borders.left = border
            borders.right = border
            borders.top = border
            borders.bottom = border
            style.borders = borders
        return style

    def save(self):
        """
        保存
        :return:
        """
        self.note.save(self.name)


if __name__ == '__main__':
    note = Note()
    note.make_style()
